Create Power BI reports

This feature is only available from Sage 200 Professional Spring 2019 version onwards.

Download the Customers report template

Download the Power BI Customers report template:

Download Power BI Customers report template (opens in a new tab)

To create a report from this report template (Sage 200 Customers Report.pbit), you need to open this file in Power BI Desktop and connect it your Sage 200 company data.

Create your report from the template

Once you have downloaded the report template, you can create your own report from the template. When you create your report from the template, you will connect it to your Sage 200 company data.

Open the report template (pbit) file in Power BI Desktop.

Tip: If you open a template file in Power BI Desktop using (Application) > Open, remember to show template files by selecting Power BI template files (*.pbit).
Sign in to your Microsoft account.
- The first time you sign in, you will also be asked to give permission for Power BI to access your Sage 200 company. Accept this to continue.
Select the parameters for the report:
- Select your Company Name.
- Select the Ageing Type for the Aged Debt report. The transactions are aged using the transaction date, the due date, or the calendar month.
Note: To change these after opening the report, use Edit Queries > Edit Parameters.
Once Power BI has opened the report template, select the tabs to view the Customers, Sales, and Aged Debt pages.
Save your report.
- Select File > Save, and save the report as a Power BI file (pbix).

Publish and share your reports

Share a Power BI report

There are a few ways to share your Power BI reports with your colleagues:

Publish the report to a workspace in the Power BI Service, so that others can view the report from a web browser.

This is the most common way to share the report. You publish the report to a workspace on the Power BI Service, so you might choose a workspace that's accessible everyone in your organisation, or a workspace that has limited access by a group of people. Your colleagues will sign in to the Power BI Service and view the report using their web browser, without needing the Power BI Desktop app.

To publish a report from the Power BI Desktop app, select Home > Publish, then choose the workspace.

Note: Publishing a report requires a Power BI Pro licence.
Share the report file with others, to view using the Power BI Desktop app.

You can share the report file (pbix) with others, so that they can view the report in the Power BI Desktop app, and upload the report to their Power BI Service.
Export the report to a Microsoft PowerPoint file or a PDF file.

Refresh the data in reports

When you publish a report, Power BI will not automatically update the data in the report from Sage 200. To make sure that your report shows up-to-date information, you'll need to refresh manually or set up a refresh schedule so that Power BI can refresh the data in your reports and dashboards for you.

See Refresh data in Power BI reports.

Edit reports

You can copy our provided report template and customise it in Power BI Desktop to suit your organisation. For example, you might want to:

Add a company logo, or change how a visualisation is filtered.
Add new visualisations, using the data fields that are provided with our reports.

To learn more about editing reports in Power BI Desktop, see the Microsoft Power BI documentation (opens in a new tab).

If you haven't used Power BI before, we recommend following the Guided learning (opens in a new tab).

Report on other data

The Power BI Customers report template that we provide only uses a small set of the data available in your Sage 200 company. If you want to create your own reports, you will probably want to access some additional company data to the customer data available in the report template.

Connecting to your company data

When you use the Sage 200 Power BI connector to access your company data, it does not connect directly to your SQL database, but uses the Web API. You need to use the Power BI connector if you want to access your data from outside your network, without exposing your SQL server to the outside world.

For help on what data is available in the Web API and how to query it, see the Sage API documentation (opens in a new tab).

Create a new Query in Power BI

The Power BI Customers report template provided already has the Power BI connector embedded and ready for use.

But if you want to access different data to the Customers report, you'll need to create a new query in Power BI.

In the following example, we'll use the provided Customers report template, and add a query to get additional bank account information.

Open the Customers report template in the Power BI Desktop application.
Select Transform Data in the ribbon.
In the Power Query Editor, in the Queries pane on the left hand side, right-click the Data > Customers node and select Duplicate.

A new Customers (2) node in the Data folder is added.
Make sure the new Customers (2) node is selected, then in Query Settings on the right, change the Name property to Banks.
In the ribbon, select the Advanced Editor.
In the Advanced Editor, change the second line to read:
```
Source = #"API Query"("banks",null,null)
```
Select Done to close the Advanced Editor.
Select Close & Apply in the Power Query Editor ribbon.

You have now added a Banks table to the report.

How to use the API Query function

The API Query function takes three parameters:

1. The API endpoint.

2. Optional filters.

3. Optional query parameters.

In this example we did not need to use any filters or parameters, so we set them to null.

For further information on available API endpoints, filters and query parameters, see the Sage API documentation (opens in a new tab).